#!/usr/bin/python
# -*- coding=utf-8 -*-
import sys
import MySQLdb
import os 
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
sys.stdout = sys.stderr  # fix runit的log没有输出


class DBManager:
    def __init__(self, db_name, host, user, passwd, port=3306, charset='utf8'):
        self.db_name = db_name
        self.host = host
        self.user = user
        self.passwd = passwd
        self.port = port
        self.charset = charset
        self.db = None
        self.cursor = None

    def __del__(self):
        """该对象删除时调用"""
        self.disconnect()

    def connect(self):
        """连接数据库"""
        if self.db is not None:
            print "[DBManager] WARRING: db wes connected!"
            return
        print "[DBManager] connect..."
        self.db = MySQLdb.connect(host=self.host,
                                  user=self.user,
                                  passwd=self.passwd,
                                  db=self.db_name,
                                  charset=self.charset,
                                  port=self.port)
        # 获取dict类型的cursor
        self.cursor = self.db.cursor(cursorclass=MySQLdb.cursors.DictCursor)

    def disconnect(self):
        """与服务器断开连接"""
        print "[DBManager] disconnect"
        if self.cursor:
            self.cursor.close()
            self.cursor = None
        if self.db:
            self.db.close()
            self.db = None

    def isconnect(self):
        """判断是否与数据库连接"""
        return self.db is not None

    def query(self, sql):
        """查询"""
        if not self.isconnect():
            print "[DBManager] WARRING: database wes not connected!"
            return None
        print "[DBManager] query: '%s'" % sql
        result = None
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
        except MySQLdb.Error, e:
            if e[0] == 2006:
                print "[DBManager] ERROR: MySQL server has gone away!"
                print "[DBManager] try connect..."
                self.disconnect()
                self.connect()
                return self.query(sql)
        return result

    def execute(self, sql):
        """执行命令"""
        if not self.isconnect():
            print "[DBManager] WARRING: database wes not connected!"
            return False
        print "[DBManager] execute: '%s'" % sql
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return True
        except MySQLdb.Error, e:
            if e[0] == 2006:
                print "[DBManager] ERROR: MySQL server has gone away!"
                print "[DBManager] try connect..."
                self.disconnect()
                self.connect()
                return self.execute(sql)
            print "[DBManager] ERROR: exec '%s' failed!" % sql
            print e
            self.db.rollback()
            return False

    def smartQuery(self, tb_name, condition=None, meet_one=False):
        """smart查询
        Args:
            tb_name: 表名
            condition: 查询条件，可以为string或dict类型
            meet_one: 当condition是dict类型时，meet_one表示满足其一条件
        Returns:
            返回查询结果
        """
        sql = "SELECT * FROM %s" % tb_name
        if condition:
            sql += " WHERE " + self.condition2str(condition, meet_one)
        return self.query(sql)

    def smartInsert(self, tb_name, info):
        kv_str = self.dict2str(info)
        sql = "INSERT INTO %s SET %s" % (tb_name, kv_str)
        print sql
        return self.execute(sql)

    def smartUpdate(self, tb_name, info, condition, meet_one=False):
        kv_str = self.dict2str(info)
        sql = "UPDATE %s SET %s" % (tb_name, kv_str)
        if condition:
            sql += " WHERE " + self.condition2str(condition, meet_one)
        print sql
        return self.execute(sql)

    def smartDelete(self, tb_name, condition, meet_one=False):
        sql = "DELETE FROM %s WHERE %s" % (tb_name, self.condition2str(condition, meet_one))
        return self.execute(sql)

    def condition2str(self, condition, meet_one):
        if not condition or type(condition) is str:
            return condition
        space = ' AND '
        if meet_one:
            space = ' OR '
        return self.dict2str(condition, space)

    def dict2str(self, info, space=','):
        if not info:
            return ""
        kv_str = ""
        for k, v in info.items():
            if type(v) is str or type(v) is unicode:
                kv_str += "%s='%s'" % (k, v)
            else:
                kv_str += "%s=%s" % (k, v)
            kv_str += space
        kv_str = kv_str[:-len(space)]
        return kv_str

    def print2format(self, info_list):
        if not info_list:
            #print "0 rows in set"
            return
        fields = info_list[0].keys()
        list_len_max = map(lambda x: len(x), fields)
        for info in info_list:
            line = map(lambda x: x if type(x) is unicode else str(x), info.values())
            line = map(lambda x: len(x) * 2 if type(x) is unicode else len(x), line)
            list_len_max = map(lambda x, y: x if (x > y) else y, list_len_max, line)
        str_row = u'+'
        for line in list_len_max:
            str_row += u'-' * (line + 2) + u'+'
        str_title = u'|'
        for field, len_max in zip(fields, list_len_max):
            str_title += u' ' + str(field).ljust(len_max) + u' |'
        #print str_row
        #print str_title
        #print str_row
        for info in info_list:
            str_line = u'|'
            for value, len_max in zip(info.values(), list_len_max):
                if type(value) is unicode:
                    str_line += u' ' + value + u' ' * (len_max - len(value) * 2)
                    str_line += u' |'
                else:
                    str_line += u' ' + str(value).ljust(len_max) + u' |'
            #print str_line
        #print str_row
        num_rows = len(info_list)
        #if num_rows <= 1:
        #    print "%d row in set" % num_rows
        #else:
        #    print "%d rows in set" % num_rows


if __name__ == '__main__':
    import csv
    import codecs
    manager = DBManager('musicpro', 'localhost', 'root', '123')
    #manager = DBManager('DB_K3_SONG_RES', 'localhost', 'root', '123')
    manager.connect()
    print manager.query("select storeid,storename,datetime from store_report where merchantid=1")

    '''
    sql = 'select name,search_py,pin_yin from ks_singer_v11 where country like "%中国%" '
    result = manager.query(sql)
    print result
    filename = "singer.csv"
    targetFile = open(filename,'w')
    targetFile.write(codecs.BOM_UTF8)
    for row in result:
        name = row["name"]
        py = row["search_py"]
        pin_yin = row["pin_yin"]
        print name
        print py
        print pin_yin
        #name = name.decode("utf-8")
        print type(name)
        targetFile.write("%s,%s,%s\n" %(name,py,pin_yin))
        #targetFile.write("%s,%s\n"%(py,pin_yin))

    '''
    manager = DBManager('klive', 'localhost', 'root', '111')
    manager.connect()

    manager.print2format(manager.smartQuery('TB_KSA_VOD_INFO'))
    manager.print2format(manager.smartQuery('TB_KSA_VOD_INFO', "mid=1"))
    manager.print2format(manager.smartQuery('TB_KSA_VOD_INFO', {'mid': 1, 'hard_sn': 'g'}))
    manager.print2format(manager.smartQuery('TB_KSA_VOD_INFO', {'mid': 1, 'hard_sn': 'g'}, meet_one=True))

    import time
    m_time = int(time.time())
    print manager.smartInsert("TB_KSA_VOD_UPGRADE_ID", {'m_time': m_time})
    manager.print2format(manager.smartQuery('TB_KSA_VOD_UPGRADE_ID', {'m_time': m_time}))

    print manager.smartUpdate("TB_KSA_VOD_UPGRADE_ID", {'alias': u'别名-阿斯达'}, {'m_time': m_time})
    manager.print2format(manager.smartQuery('TB_KSA_VOD_UPGRADE_ID', {'m_time': m_time}))

    print manager.smartDelete("TB_KSA_VOD_UPGRADE_ID", {'m_time': m_time})
    manager.print2format(manager.smartQuery('TB_KSA_VOD_UPGRADE_ID', {'m_time': m_time}))
